const { pool } = require('../config/database');

class GoodsCategory {
  /**
   * 获取所有商品分类
   */
  static async getAllCategories() {
    const connection = await pool.getConnection();
    try {
      const [categories] = await connection.execute(`
        SELECT gc.id, gc.sort, gc.icon, gc.name, gc.is_show_backstage,
               COUNT(g.id) as goods_count
        FROM goods_categories gc
        LEFT JOIN goods g ON gc.id = g.category_id
        GROUP BY gc.id, gc.sort, gc.icon, gc.name, gc.is_show_backstage
        ORDER BY gc.sort ASC, gc.id ASC
      `);

      return {
        total: categories.length,
        list: categories.map(cat => ({
          id: cat.id,
          sort: cat.sort || 0,
          icon: cat.icon || "",
          name: cat.name || "",
          is_show_backstage: cat.is_show_backstage || 0,
          goods_count: cat.goods_count || 0
        }))
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 根据ID获取单个商品分类
   */
  static async getById(categoryId) {
    const connection = await pool.getConnection();
    try {
      const [categories] = await connection.execute(`
        SELECT gc.id, gc.sort, gc.icon, gc.name, gc.is_show_backstage,
               COUNT(g.id) as goods_count
        FROM goods_categories gc
        LEFT JOIN goods g ON gc.id = g.category_id
        WHERE gc.id = ?
        GROUP BY gc.id, gc.sort, gc.icon, gc.name, gc.is_show_backstage
      `, [categoryId]);

      if (categories.length === 0) {
        return null;
      }

      const category = categories[0];
      return {
        id: category.id,
        sort: category.sort || 0,
        icon: category.icon || "",
        name: category.name || "",
        is_show_backstage: category.is_show_backstage || 0,
        goods_count: category.goods_count || 0
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 创建新的商品分类
   */
  static async create(categoryData) {
    const connection = await pool.getConnection();
    try {
      const {
        name,
        icon = '',
        sort = 0,
        is_show_backstage = 0
      } = categoryData;

      // 验证必需字段
      if (!name) {
        throw new Error('分类名称为必填项');
      }

      // 创建分类
      const [result] = await connection.execute(`
        INSERT INTO goods_categories (name, icon, sort, is_show_backstage)
        VALUES (?, ?, ?, ?)
      `, [name, icon, sort, is_show_backstage]);

      return {
        id: result.insertId,
        name,
        icon,
        sort,
        is_show_backstage,
        goods_count: 0
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 更新商品分类
   */
  static async update(categoryId, categoryData) {
    const connection = await pool.getConnection();
    try {
      // 检查分类是否存在
      const [categories] = await connection.execute(
        'SELECT id FROM goods_categories WHERE id = ?',
        [categoryId]
      );

      if (categories.length === 0) {
        throw new Error('商品分类不存在');
      }

      // 构建更新字段
      const updateFields = [];
      const updateValues = [];

      const allowedFields = ['name', 'icon', 'sort', 'is_show_backstage'];

      allowedFields.forEach(field => {
        if (categoryData[field] !== undefined) {
          updateFields.push(`${field} = ?`);
          updateValues.push(categoryData[field]);
        }
      });

      if (updateFields.length === 0) {
        throw new Error('没有提供要更新的字段');
      }

      updateValues.push(categoryId);

      // 执行更新
      const [result] = await connection.execute(
        `UPDATE goods_categories SET ${updateFields.join(', ')} WHERE id = ?`,
        updateValues
      );

      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  /**
   * 删除商品分类
   */
  static async delete(categoryId) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      // 检查分类是否存在
      const [categories] = await connection.execute(
        'SELECT id, name FROM goods_categories WHERE id = ?',
        [categoryId]
      );

      if (categories.length === 0) {
        throw new Error('商品分类不存在');
      }

      // 检查是否有商品使用此分类
      const [goods] = await connection.execute(
        'SELECT COUNT(*) as count FROM goods WHERE category_id = ?',
        [categoryId]
      );

      if (goods[0].count > 0) {
        throw new Error(`该分类下还有 ${goods[0].count} 个商品，无法删除`);
      }

      // 删除分类
      const [result] = await connection.execute(
        'DELETE FROM goods_categories WHERE id = ?',
        [categoryId]
      );

      await connection.commit();

      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 根据名称模糊查找商品分类
   */
  static async searchByName(name) {
    const connection = await pool.getConnection();
    try {
      const [categories] = await connection.execute(`
        SELECT gc.id, gc.sort, gc.icon, gc.name, gc.is_show_backstage,
               COUNT(g.id) as goods_count
        FROM goods_categories gc
        LEFT JOIN goods g ON gc.id = g.category_id
        WHERE gc.name LIKE ?
        GROUP BY gc.id, gc.sort, gc.icon, gc.name, gc.is_show_backstage
        ORDER BY gc.sort ASC, gc.id ASC
      `, [`%${name}%`]);

      return {
        total: categories.length,
        list: categories.map(cat => ({
          id: cat.id,
          sort: cat.sort || 0,
          icon: cat.icon || "",
          name: cat.name || "",
          is_show_backstage: cat.is_show_backstage || 0,
          goods_count: cat.goods_count || 0
        }))
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 获取分类选项（用于下拉选择）
   */
  static async getCategoryOptions() {
    const connection = await pool.getConnection();
    try {
      const [categories] = await connection.execute(`
        SELECT id, name
        FROM goods_categories
        ORDER BY sort ASC, id ASC
      `);

      return categories.map(cat => ({
        label: cat.name,
        value: cat.id
      }));
    } finally {
      connection.release();
    }
  }
}

module.exports = GoodsCategory;
